# 完整代码
import requests
from bs4 import BeautifulSoup

def parse_fleet( url):
    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko'}
    f = requests.get(url, headers=headers)
    soup= BeautifulSoup(f.text, "lxml")
    fleets=[]
    fleets_rows= soup.select('table[class="prettyTable fullWidth"] > tr')
    #print(fleets_rows)
    for row in fleets_rows:
        try:
            fields= row.select('a')
            #print(fields)
            if len(fields) == 3:
                fleet_id= fields[0].text
                airport_s= fields[1].text
                airport_d= fields[2].text
                airplane= ""
            elif len(fields) == 4:
                fleet_id= fields[0].text
                airplane= fields[1].text
                airport_s= fields[2].text
                airport_d= fields[3].text

            airline= fleet_id[:3]
            if len(airport_s)==10 :
                airport_s3= airport_s[:3]
                airport_s4= airport_s[6:]
            else:
                airport_s3= airport_s4= airport_s

            if len(airport_d)==10 :
                airport_d3= airport_d[:3]
                airport_d4= airport_d[6:]
            else:
                airport_d3= airport_d4= airport_d

            fleet= {
                        "airline": airline,
                        "id" : fleet_id,
                        "airplane" : airplane,
                        "airport_s3" : airport_s3,
                        "airport_s4" : airport_s4,
                        "airport_d3" : airport_d3,
                        "airport_d4" : airport_d4
                    }
            fleets.append(fleet)
            print("Fleet : "+ fleet_id)
        except  Exception as e:
            print('Error: '+ str(e))
    
    return fleets

import mysql.connector
def save_fleet(fleets):
    try:
        cnx = mysql.connector.connect(user='flightdata', password='flightdata',
                                  host='127.0.0.1',
                                  database='flightdata')
        cursor = cnx.cursor()
        cursor.execute("create table if not exists fleet(id varchar(50) primary key, airline varchar(10), \
                       airplane varchar(10),  airport_s3 varchar(10) ,airport_s4 varchar(10) , \
                      airport_d3 varchar(10) ,airport_d4 varchar(10));")
        add_fleet = "INSERT ignore INTO fleet  \
                      (id, airline, airplane, airport_s3,airport_s4, airport_d3,airport_d4) \
                      VALUES (%s,%s,%s,%s,%s,%s,%s)"
        for f in fleets:
            #print(f['id'])
            cursor.execute(add_fleet, (f['id'], f['airline'],f['airplane'],f['airport_s3'],f['airport_s4'], 
                                       f['airport_d3'],f['airport_d4']))

        cnx.commit()

        cursor.close()
        cnx.close()
    except  Exception as e:
        print('Error: '+ str(e))


if __name__ == "__main__":
    # 航线数据 https://flightaware.com/live/fleet/
    #airlines={"CSN": 270, "CES":226, "CCA":212}
    airlines= {"AAL": 456, "DLH": 120, "UAL":434,"AFR":70, "UAE":136 }
    for name, count in airlines.items():
        for offset in range(0, count, 40):
            # url: http://flightaware.com/live/fleet/CSN?;offset=0;order=ident;sort=ASC
            url= "http://flightaware.com/live/fleet/"+name+"?;offset="+str(offset)+";order=ident;sort=ASC"
            print(url)
            fleets= parse_fleet(url)
            print( "Get fleets: "+ str(len(fleets)))
            save_fleet(fleets)    